CSV Query Builder

The CSV Query Builder is an interactive dialog that allows the user to quickly setup a CSV file parsing, so it can be queried as a usual table. It uses the TEXTTABLE stored procedure to perform the parsing. For more information on this procedure please refer to the FROM Clause manual.

File Data Source

There are couple of configuration options:

• Data source: a dropdown list of existing file data sources.
• File format: the content type of the source files: only CSV is supported.
• File name: the name of the file in the data source.
• Encoding: file encoding, default is "UTF-8".

Rest Web Service Data Source

There are couple of configuration options:

• Datasource: a dropdown list of existing web data sources.
• End point: destination URL to perform requests.
• Action: HTTP action: GET or POST.
• Request content type: the media type for the request.
• Encoding: file encoding, default is "UTF-8".
• Request header: HTTP request header.
• Request: HTTP request body, for POST actions only.
• Response format: the content type of the response: only CSV is supported.
• Request rate limit: most Web Services have a request rate limit and its violation causes a requests to be declined. This configuration option defines a request rate limit per second for the wizard only, so the previews are refreshed respecting this limit.

CSV File Import Options

• Data source is a file data source, which is used to retrieve csv files.
• File name is the name of the file that should be queried.
• Table variable is the name of the table in the SQL select.
• Dynamic parsing splits the CSV file by delimiter characters and additional options.
  • Delimiter sets the field delimiter character to use. Defaults to ','.
  • Selector specifies that delimited lines should only be selected if the line begins with the selector string followed by a delimiter. The selector value is a valid column value. If this field is specified, a SELECTOR may also be specified for column values. A column SELECTOR argument will select the nearest preceding text line with the given SELECTOR prefix and select the value at the given 1-based integer position (which includes the selector itself). If no such text line or position with a given line exists, a null value will be produced.
  • Quote sets the quote (or qualifier) character used to wrap field values. Defaults to '"'.
  • Escape sets the escape character to use if no quoting character is in use. This is used in situations where the delimiter or new line characters are escaped with a preceding character, e.g. \.
  • Header specifies the text line number (counting every new line) on which the column names occur. All lines prior to the header will be skipped. If HEADER is specified, then the header line will be used to determine the TEXTTABLE column position by case-insensitive name matching. This is especially useful in situations where only a subset of the columns are needed. If the HEADER value is not specified, it defaults to 1. If HEADER is not specified, then columns are expected to match positionally with the text contents.
  • Skip specifies the number of text lines (counting every new line) to skip before parsing the contents. HEADER may still be specified with SKIP.
• Fixed width parsing simply divides the input file into a set of fixed-length strings.
  • No row delimiter indicates that fixed parsing should not assume the presence of newline row delimiters.
  • Width indicates the fixed-width length of a column in characters - not bytes. The CR NL newline value counts as a single character.
  • No trim specifies that the text value should not be trimmed of all leading and trailing white space.

Syntax Rules:

• If width is specified for one column, it must be specified for all columns and be a non-negative integer.
• If width is specified, then fixed width parsing is used and ESCAPE, QUOTE, SELECTOR, and HEADER should not be specified.
• If width is not specified, then no row delimiter cannot be used.
• Columns must have unique names, duplicates are not allowed. Please note, case is ignored, so "Name" and "name" are considered to be equal.

As a result, a script like shown below is generated and shown in the SQL editor.

SELECT stock.*
FROM 
    (CALL csv.getTextFiles('Adminschulung_Vertriebsauftrag.csv')) f,
    TEXTTABLE(f.file 
      COLUMNS Vertriebsauftrag_ID string,
          Bestelldatum string,
          Faelligkeitsdatum string,
          Auslieferungsdatum string, 
          Online_Bestellung string, 
          Haendler string, 
          Region string, 
          Kontinent string, 
          Land string, 
          Region_Hierarchie string, 
          Haendler_Hierarchie string
      DELIMITER ';' 
      SKIP 1
    ) stock

Adding and removing columns

There are four buttons to the right of the columns list that allow the user to easily add and remove columns:

• Add all detected columns : This option will automatically detect columns based on the first valid line (not including skipped lines) of the document. Up to 1000 columns are supported for automatic creation. All automatically added columns will have the data type STRING and no selector.
• Add a column : This option will add a single column with a generic name new_column_x.
• Remove selected column : Removes the selected column.
• Remove all columns : Removes all columns (does nothing if the list is empty).

Source data preview

Source data preview panel shows the original content of the file or web service response.
Note: the size of preview is limited to 64Kb.

Reference manual pages:

• FROM Clause